/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.LanguageTargetKey;
import com.google.api.adwords.starterkit.db.structures.LanguageTargetStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;
import com.google.api.adwords.starterkit.wrappers.CampaignWrapper;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;

/**
 * The Class LanguageTargetTable encapsulates most of the access to  
 * LanguageTarget table in DB.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class LanguageTargetTable {
  private PreparedStatement prepReplace;
  private PreparedStatement prepLoad;
  private PreparedStatement prepDel;
  private Connection conn;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  public void initTable() {
    try { 
      Statement stat = conn.createStatement();      
      stat.executeUpdate(
            "CREATE TABLE IF NOT EXISTS LanguageTargets "
          + "   ("
          + "     LocalCampaignId INTEGER, "
          + "     CampaignId LONG, "
          + "     Language STRING default '', "
          + "     PRIMARY KEY (LocalCampaignId, Language)"
          + "   ); ");
      stat.executeUpdate(
            "CREATE INDEX IF NOT EXISTS LanguageTargetsCampaignIdIndex "
          + "ON LanguageTargets (CampaignId);");
      
      prepLoad = conn.prepareStatement(
          "SELECT * FROM LanguageTargets WHERE LocalCampaignId = ?;"); 

      prepReplace = conn.prepareStatement(
          "REPLACE INTO LanguageTargets (LocalCampaignId, CampaignId, Language)" 
          + " VALUES (?, ?, ?);");

      prepDel = conn.prepareStatement(
          "DELETE FROM LanguageTargets WHERE LocalCampaignId = ? ");        
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }        
  }

  /**
   * Instantiates a new platform utils.
   *
   * @param conn the DB connection
   */
  public LanguageTargetTable(Connection conn) {
    this.conn = conn;
    initTable();
  }
  
  /**
   * Convert the ResultSet format from DB into LanguageTargetStructure format 
   *
   * @param rs the ResultSet assigned to LanguageTarget table
   * @return the database row in LanguageTargetStructure format 
   */  
  public LanguageTargetStructure getResultSet(ResultSet rs) {
    LanguageTargetStructure languageTargetStructure = null;
    try {
       languageTargetStructure 
           = new LanguageTargetStructure(rs.getInt("LocalCampaignId"), 
               rs.getLong("CampaignId"), 
               rs.getString("Language"));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return languageTargetStructure;    
  }
  
  /**
   * Load a set of LanguageTarget from DB.
   *
   * @param languageTargetKey the key that specifies the local campaign id
   * @return a set of LanguageTarget assigned to the local campaign id
   */
  public HashSet<LanguageTargetStructure> get(
      LanguageTargetKey languageTargetKey) {
    HashSet<LanguageTargetStructure> langTargets 
        = new HashSet<LanguageTargetStructure>();
    ResultSet rs = null;
    try {
      prepLoad.setInt(1, languageTargetKey.getLocalCampaignId());
      rs = prepLoad.executeQuery();
      while (rs.next()) {
        LanguageTargetStructure languageTargetStructure = getResultSet(rs);
        langTargets.add(languageTargetStructure);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return langTargets;
  }
  
  /**
   * Save set of Language Targets to DB
   *
   * @param languageTargets the set of Language Targets
   */
  public void save(HashSet<LanguageTargetStructure> languageTargets) {        
    for (Iterator<LanguageTargetStructure> i 
        = languageTargets.iterator(); i.hasNext(); ) {      
      LanguageTargetStructure languageTargetStructure = i.next();
      try {
        prepReplace.setInt(1, languageTargetStructure.getLocalCampaignId());
        prepReplace.setLong(2, languageTargetStructure.getCampaignId());
        prepReplace.setString(3, languageTargetStructure.getLanguage());
        prepReplace.executeUpdate();
      } catch (SQLException e) {
        DumpExceptions.dumpSQLException(e);
      }         
    }
  }
  
  /**
   * Delete a LanguageTarget from DB
   *
   * @param languageTargetKey the LanguageTarget to be deleted
   */
  public void del(LanguageTargetKey languageTargetKey) {
    try {
      prepDel.setLong(1, languageTargetKey.getLocalCampaignId());
      prepDel.execute();      
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }       
  }  
  
  /**
   * Get all campaign ids from AdWords associated to campaigns just created 
   * by Starter Kit
   *
   * @return a map between campaign id and local campaign id 
   */
  public HashMap<Long, Integer> getNewCampaignsIds() {
    HashMap<Long, Integer> campaignsIds = new HashMap<Long, Integer>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT DISTINCT ca.localId, ca.CampaignId FROM Campaigns ca, "
          + "LanguageTargets gt WHERE gt.LocalCampaignId = ca.LocalId AND "
          + "gt.CampaignId = 0;");
      while (rs.next()) {
        campaignsIds.put(rs.getLong("CampaignId"), rs.getInt("localId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return campaignsIds;
  }

  /**
   * Update campaigns ids at geo target table.
   */
  public void updateCampaignsIds() {
    log.debug("Updating CampaignId at LanguageTargets table");
    HashMap<Long, Integer> newCampaignsId = getNewCampaignsIds();
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE LanguageTargets SET CampaignId = ? " +
          "WHERE LocalCampaignId = ?");
      int count = 0;
      for (long campaignId : newCampaignsId.keySet()) {
        int localCampaignId = newCampaignsId.get(campaignId);
        prep.setLong(1, campaignId);
        prep.setInt(2, localCampaignId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }    
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }
  
  /**
   * Return all LanguageTargets modified during the last Starter Kit execution
   * 
   * @return the ResultSet that contains LanguageTargets modified 
   */
  public ResultSet getModified() {
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
    prep = conn.prepareStatement(
        "SELECT lt.CampaignId CampaignId, lt.Language Language " 
      + "FROM LanguageTargets lt, Campaigns ca " 
      + "WHERE ca.IsLanguageModified = 1 " 
      + "AND ca.localId = lt.LocalCampaignId");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }
  
  /**
   * Links a set of LanguageTarget to a campaign defined at CampaignWrapper
   *
   * @param campaignWrapper the campaignWrapper representing a campaign
   * @param languageTargets a set of LanguageTarget to be linked to the campaign
   */
  public static void link(CampaignWrapper campaignWrapper, 
      HashSet<LanguageTargetStructure> languageTargets) {
    for (Iterator<LanguageTargetStructure> i 
        = languageTargets.iterator(); i.hasNext(); ) {
      LanguageTargetStructure languageTargetStructure = i.next();
      languageTargetStructure.setCampaignId(campaignWrapper.getCampaignId());
      languageTargetStructure.setLocalCampaignId(campaignWrapper.getLocalId());
    }        
  }
  
  /**
   * Convert a set of LanguageTargetSettings structure into a set of Strings 
   *
   * @param langs A set of LanguageTargetting converted to a String
   * @param langTargets the original GeoTargetSettings 
   */
  private static void convert(HashSet<String> langs, 
      HashSet<LanguageTargetStructure> langTargets) {
    for (Iterator<LanguageTargetStructure> i 
        = langTargets.iterator(); i.hasNext(); ) {
      LanguageTargetStructure languageTarget = i.next();
      langs.add(languageTarget.getLanguage().toLowerCase());
    }
  }
    
  /**
   * Verifies if two GeoTargetList are equals
   *
   * @param a the GeoTargetList a
   * @param b the GeoTargetList b
   * @return true, if equals
   */
  public static boolean equals(HashSet<LanguageTargetStructure> a, 
      HashSet<LanguageTargetStructure> b) {
    HashSet<String> langA = new HashSet<String>();
    HashSet<String> langB = new HashSet<String>();
    
    convert(langA, a);
    convert(langB, b);
    
    if (langA.containsAll(langB) && langB.containsAll(langA)) {
      return true;
    }
    return false;
  }  
  
  public void saveBatch(LanguageTargetStructure languageTargetStructure) {
    try {
      prepReplace.setInt(1, languageTargetStructure.getLocalCampaignId());
      prepReplace.setLong(2, languageTargetStructure.getCampaignId());
      prepReplace.setString(3, languageTargetStructure.getLanguage());
      prepReplace.addBatch();    
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  public void executeBatch() {
    try {
      prepReplace.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Reset all campaigns status flags to its default values
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Campaigns SET IsLanguageModified = 0;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }
}
